﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

/// <summary>
/// Summary description for SQLServer
/// </summary>
public class SQLServer
{
	public SQLServer()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static DataTable GetDataTable(string psTable, string psFilter)
    {
        string sSQLServerCn = ConfigurationManager.ConnectionStrings["ACC_NEW"].ConnectionString;
        SqlConnection cnSQLServer = new SqlConnection(sSQLServerCn);

        DataTable dtSQLServer = new DataTable();

        using (cnSQLServer)
        {
            cnSQLServer.Open();
            string sSQL = "SELECT * FROM " + psTable + " WHERE " + psFilter.Replace("='ZZ'", " IS NULL").Replace("='ALL REGIONS'", " IS NULL");
            using (SqlDataAdapter da = new SqlDataAdapter(sSQL, cnSQLServer))
            {
                da.SelectCommand.CommandTimeout = 0;
                da.Fill(dtSQLServer);
            }
        }
        return dtSQLServer;
    }

    public static DataTable GetDataTable(string psSQL)
    {
        string sSQLServerCn = ConfigurationManager.ConnectionStrings["ACC_NEW"].ConnectionString;
        SqlConnection cnSQLServer = new SqlConnection(sSQLServerCn);

        DataTable dtSQLServer = new DataTable();

        using (cnSQLServer)
        {
            cnSQLServer.Open();
            using (SqlDataAdapter da = new SqlDataAdapter(psSQL, cnSQLServer))
            {
                da.SelectCommand.CommandTimeout = 0;
                da.Fill(dtSQLServer);
            }
        }
        return dtSQLServer;
    }

    public static DataTable GetMTDSalesDataTable(string psProc, string psFilter, DateTime pdtDate)
    {
        string sSQLServerCn = ConfigurationManager.ConnectionStrings["ACC_NEW"].ConnectionString;
        SqlConnection cnSQLServer = new SqlConnection(sSQLServerCn);

        DataTable dtSQLServer = new DataTable();

        using (cnSQLServer)
        {
            cnSQLServer.Open();
            string sSQL = string.Format("EXEC dbo.{0}_MTD_STATS '{1}', '{2}'", psProc, psFilter, pdtDate);
            using (SqlDataAdapter da = new SqlDataAdapter(sSQL, cnSQLServer))
            {
                da.SelectCommand.CommandTimeout = 0;
                da.Fill(dtSQLServer);
            }
        }
        return dtSQLServer;
    }

    public DataTable TempManagersList()
    {
        //string sSQL = "SELECT     REGION,  '\xa0\xa0\xa0\xa0' + acct_mgr txt, acct_mgr + ':2' val, 2 LVL, 1 ORD  FROM         (SELECT     TOP (100) PERCENT REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR)                                                 - 2) ELSE acct_mgr END AS acct_mgr                         FROM          SCORE_ACC_PROPERTIES.PROPERTIES                         GROUP BY REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR) - 2)                                                 ELSE acct_mgr END                         ORDER BY REGION, ACCT_MGR) AS derivedtbl_  GROUP BY REGION, acct_mgr  UNION  SELECT DISTINCT REGION,REGION + '-------------------------------------',REGION+':0',  1, 0  FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1  ORDER BY 1,5,3,4";
        string sSQL = SQLFiles.ConstructSQL("Managers", "");
        return GetDataTable(sSQL);
    }

    public DataTable TempManagersWithSalesList()
    {
        //string sSQL = "SELECT     REGION,  '\xa0\xa0\xa0\xa0' + acct_mgr txt, acct_mgr + ':2' val, 2 LVL, 1 ORD  FROM         (SELECT     TOP (100) PERCENT REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR)                                                 - 2) ELSE acct_mgr END AS acct_mgr                         FROM          SCORE_ACC_PROPERTIES.PROPERTIES                         GROUP BY REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR) - 2)                                                 ELSE acct_mgr END                         ORDER BY REGION, ACCT_MGR) AS derivedtbl_  GROUP BY REGION, acct_mgr  UNION  SELECT DISTINCT REGION,REGION + '-------------------------------------',REGION+':0',  1, 0  FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1  ORDER BY 1,5,3,4";
        string sSQL = SQLFiles.ConstructSQL("ManagersWithSales", "");
        return GetDataTable(sSQL);
    }

    public DataTable TempPortfoliosWithSalesList()
    {
        //string sSQL = "SELECT     REGION,  '\xa0\xa0\xa0\xa0' + acct_mgr txt, acct_mgr + ':2' val, 2 LVL, 1 ORD  FROM         (SELECT     TOP (100) PERCENT REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR)                                                 - 2) ELSE acct_mgr END AS acct_mgr                         FROM          SCORE_ACC_PROPERTIES.PROPERTIES                         GROUP BY REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR) - 2)                                                 ELSE acct_mgr END                         ORDER BY REGION, ACCT_MGR) AS derivedtbl_  GROUP BY REGION, acct_mgr  UNION  SELECT DISTINCT REGION,REGION + '-------------------------------------',REGION+':0',  1, 0  FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1  ORDER BY 1,5,3,4";
        string sSQL = SQLFiles.ConstructSQL("PortfoliosWithSales", "");
        return GetDataTable(sSQL);
    }

    public static String GetLocation(string psTRAC_ID)
    {
        string sSQL = SQLFiles.ConstructSQL("FullAddress", psTRAC_ID);
        return GetDataTable(sSQL).Rows[0][0].ToString();
    }


    public DataTable TempMarketsList()
    {
        //string sSQL = "SELECT     REGION,  '\xa0\xa0\xa0\xa0' + acct_mgr txt, acct_mgr + ':2' val, 2 LVL, 1 ORD  FROM         (SELECT     TOP (100) PERCENT REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR)                                                 - 2) ELSE acct_mgr END AS acct_mgr                         FROM          SCORE_ACC_PROPERTIES.PROPERTIES                         GROUP BY REGION, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR) - 2)                                                 ELSE acct_mgr END                         ORDER BY REGION, ACCT_MGR) AS derivedtbl_  GROUP BY REGION, acct_mgr  UNION  SELECT DISTINCT REGION,REGION + '-------------------------------------',REGION+':0',  1, 0  FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1  ORDER BY 1,5,3,4";
        string sSQL = SQLFiles.ConstructSQL("Markets", "");
        return GetDataTable(sSQL);
    }
}